﻿CREATE PROCEDURE [dbo].[wikiCategories_get_listing]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int = 0,
    @name nvarchar(256) = null
AS
    SELECT 
        WikiCategories.CategoryId,
        Name,
        CountPages = (SELECT Count(1) From WikiPages Where WikiPages.CategoryId = WikiCategories.CategoryId) 
    From WikiCategories
    Where 
    (@name IS NULL OR WikiCategories.Name like '%' + @name + '%')
    ORDER BY 
        CASE WHEN @column = 'Name' AND @direction = 1 THEN WikiCategories.Name END DESC,
        CASE WHEN @column = 'Name' AND @direction = 0 THEN WikiCategories.Name END ASC,
        CASE WHEN @column = 'CountPages' AND @direction = 1 THEN  (SELECT Count(1) From WikiPages Where WikiPages.CategoryId = WikiCategories.CategoryId) END DESC,
        CASE WHEN @column = 'CountPages' AND @direction = 0 THEN  (SELECT Count(1) From WikiPages Where WikiPages.CategoryId = WikiCategories.CategoryId) END ASC

    OFFSET ((@page -1)*@size) ROWS 
    FETCH NEXT @size ROWS ONLY;
    
Declare 
@TotalCount int
 SELECT @TotalCount= Count(DISTINCT WikiCategories.CategoryId)
	FROM WikiCategories
	where 
		(@name is NULL OR WikiCategories.Name like '%'+ @name + '%')

RETURN @TotalCount;
